package com.api.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.api.base.ExcelConfig;
import com.api.domain.ExcelData;

/**
 * 读取excel表中数据
 * 
 * @author Administrator
 *
 */
public class ReadExcelUtils {

	/**
	 * 读取excel文件中数据
	 * 
	 * @param filePath
	 * @return
	 */
	public List<ExcelData> readExcel(String filePath) {
		if (filePath == null || ExcelConfig.EMPTY.equals(filePath)) {
			return null;
		} else {
			String fileSuffix = getFileSuffix(filePath);
			if (!ExcelConfig.EMPTY.equals(fileSuffix)) {
				if (ExcelConfig.OFFICE_EXCEL_2003_FILESUFFIX.equals(fileSuffix)) {
					try {
						return null;
					} catch (Exception e) {
						throw new RuntimeException(e);
					}
				} else if (ExcelConfig.OFFICE_EXCEL_2010_FILESUFFIX.equals(fileSuffix)) {
					try {
						return readXlsx(filePath);
					} catch (IOException e) {
						throw new RuntimeException(e);
					}
				}
			} else {
				System.out.println(filePath + "此文件不存在！");
			}
		}
		return null;
	}

	public static String getFileSuffix(String filePath) {
		if (filePath == null || ExcelConfig.EMPTY.equals(filePath.trim())) {
			return ExcelConfig.EMPTY;
		}
		if (filePath.contains(ExcelConfig.POINT)) {
			return filePath.substring(filePath.lastIndexOf(ExcelConfig.POINT) + 1, filePath.length());
		}
		return ExcelConfig.EMPTY;
	}

	/**
	 * 读取xlsx扩展名文件数据
	 * 
	 * @param filePath
	 * @return
	 * @throws IOException
	 */
	public List<ExcelData> readXlsx(String filePath) throws IOException {
		InputStream in = new FileInputStream(filePath);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
		ExcelData excelData = null;
		List<ExcelData> list = new ArrayList<ExcelData>();

		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}
			for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow != null) {
					excelData = new ExcelData();
					XSSFCell ID = xssfRow.getCell(0);
					XSSFCell isExec = xssfRow.getCell(1);
					XSSFCell caseName = xssfRow.getCell(2);
					XSSFCell type = xssfRow.getCell(3);
					XSSFCell classification = xssfRow.getCell(4);
					XSSFCell coditionsType = xssfRow.getCell(5);
					XSSFCell conditions = xssfRow.getCell(6);
					XSSFCell conditionsParams = xssfRow.getCell(7);
					XSSFCell conditionsValue = xssfRow.getCell(8);
					XSSFCell reqUrl = xssfRow.getCell(9);
					//
					XSSFCell expectedUrl = xssfRow.getCell(10);
					XSSFCell expectedParams = xssfRow.getCell(11);
					XSSFCell userName = xssfRow.getCell(12);
					// XSSFCell sqlOrder = xssfRow.getCell(13);
					XSSFCell sql = xssfRow.getCell(13);
					XSSFCell expectedValue = xssfRow.getCell(14);

					excelData.setID(getValue(ID));
					excelData.setIsExec(getValue(isExec));
					excelData.setCaseName(getValue(caseName));
					excelData.setType(getValue(type));

					excelData.setClassification(getValue(classification));
					excelData.setCoditionsType(getValue(coditionsType));
					excelData.setConditions(getValue(conditions));
					excelData.setConditionsParams(getValue(conditionsParams));
					excelData.setConditionsValue(getValue(conditionsValue));
					excelData.setReqUrl(getValue(reqUrl));

					excelData.setExpectedUrl(getValue(expectedUrl));
					excelData.setExpectedParams(getValue(expectedParams));
					excelData.setUserName(getValue(userName));
					// excelData.setSqlOrder(getValue(sqlOrder));
					excelData.setSql(getValue(sql));
					excelData.setExpectedValue(getValue(expectedValue));
					list.add(excelData);
				}
			}
		}
		return list;
	}

	private String getValue(XSSFCell xssfCell) {
		if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfCell.getBooleanCellValue());
		} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(xssfCell.getNumericCellValue());
		} else {
			return String.valueOf(xssfCell.getStringCellValue());
		}
	}

}
